/**
 * Created by guany on 2017/1/8.
 */

var sqls = {
    login: 'select * from v_user_info where user_name = ? and password = ?',
    getMenuCodeByID:'select code from t_menu t1 inner join t_role_menu t2 on t1.id = t2.menu_id where t2.role_id = ?',
    findAllMenu:'select code,name,t1.id as id,p_id from t_menu t1 inner join t_role_menu t2 on t1.id = t2.menu_id where t2.role_id = ?',
    selecOne:'select * from t_company;select id from t_attachment where file_type = 1',
    visitorList:'select count(id) as total from t_visitor;select name,state,plan_start_time,plan_end_time from t_visitor limit ?,?',
    attendancdList:'select user_id from t_record where user_origin = 1 and in_time between ? and ? group by user_id;select t3.*,t4.name from (select t1.user_id as user_id, real_name, min(t1.in_time) as first_time, max(t1.in_time) as last_time,validate_way,t1.id as id,id_num,dep_id from t_record t1 inner join t_user t2 on t1.user_id = t2.id where t1.user_origin = 1 and t1.in_time between ? and ? group by t1.user_id) t3 inner join t_dep t4 on t3.dep_id = t4.id limit ?,?',
    getImg:'select file_path from t_attachment where id = ?',
    getCompanyID:'select id from t_company',
    saveImg:'insert into t_attachment (file_name,file_path,file_ext,file_type,obj_id,creater,create_time) values (?,?,?,?,?,?,?)',
    queryImgByID:'select * from t_attachment where id = ?',
    deleteImg:'delete from t_attachment where id = ?',
    getInfo:'select t3.*,count(t4.id) as count from t_user t4 inner join (select t2.id,t1.id as userid,t2.leader,t1.real_name as leaderName,sort,t2.name,t2.p_id from t_user t1 inner join (select * from t_dep where id = ? or p_id = ?) t2 on t1.id = t2.leader) t3 on t3.id = t4.dep_id group by dep_id',
    selectChildDep:'select create_time,dep_num,id,is_disabled,leader,name,p_id,rule_id,sort from t_dep where p_id = ?',
    findUserInfoByDepId:'select create_time,dep_id,face_id,finger_id,id,id_card,id_num,is_disabled,password,position,real_name,role_id,sub_phone,tel,update_by,update_time,user_name from t_user where dep_id = ?',
    selectList:'select * from t_dep where p_id = ?',
    fetchListCount:'select count(t1.id) as total from t_visitor t1 inner join (select t_user.id from t_user inner join t_dep on t_user.dep_id = t_dep.id where t_dep.p_id = ? or t_dep.id = ?) t2 on t1.inviter = t2.id ',
    fetchList:'select t1.* from t_visitor t1 inner join (select t_user.id from t_user inner join t_dep on t_user.dep_id = t_dep.id where t_dep.p_id = ? or t_dep.id = ?) t2 on t1.inviter = t2.id '
};

module.exports = sqls;